# coding: utf-8

# NOTE by huangkai 2018-07-17
# 汇币清理
# 1、骨灰用户账户余额如果不是特别离谱（小于1000）就保留
# 骨灰用户全部小于1000，不需要额外处理
# uid       bid_point
# 3074133	138
# 3123620	338
# 3053380	338
# 3074372	338
# 104912	486
# 48284	600
# 72797	624
# 36159	724
# 3047474	786

# 2、从未付费过的用户汇币清零，送3张有效期到8月16日有效的代金券
# 3、付费过的用户导出数据运营人肉排查，主要排查历史累计充值＜剩余的
# 4、虚拟用户汇币清零
# 5、内部账户汇币全部清零


import os
import datetime
import math
import set_env_path

from sqlalchemy import create_engine
from sqlalchemy.sql import text

from common.wechat_util import update_users_tag

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def main():
    # 骨灰用户ids
    long_long_time_ago_user_ids = [
        3059152, 36832, 48284, 3053380, 53369, 3043669, 102448, 3074133,
        72797, 128132, 131220, 114661, 3045207, 36159, 3074372, 3123620,
        3052115, 3096249, 3044678, 3047474, 41088, 104912, 3046014,
        29829, 23246
    ]

    # 绑定了微信的openids
    wx_openids = []
    wx_tag_id = 151

    # 清空未付费用户的汇币
    update_not_bill_pack_sql = text("""
        update
            pack
        set
            bid_point = 0
        where
            uid not in (
                select DISTINCT uid from bill where
                    bill.type = 1
                    and bill.sub_type in ( 2, 3 )
                    and bill.status = 200 )
            and uid not in :long_long_time_ago_user_ids;
    """)
    conn.execute(
        update_not_bill_pack_sql,
        long_long_time_ago_user_ids = long_long_time_ago_user_ids,
    )

    # 未付费用户赠送代金券
    not_bill_user_ids_sql = text("""
        select
            id, wx_openid
        from
            user
        where
            id not in (
                select DISTINCT uid from bill where
                    bill.type = 1
                    and bill.sub_type in ( 2, 3 )
                    and bill.status = 200 )
            and id not in :long_long_time_ago_user_ids
            and type=100
            and deleted_at=0
            and is_internal=0
            and is_virt=0
            and black=0;
    """)
    not_bill_user_ids_result = conn.execute(
        not_bill_user_ids_sql,
        long_long_time_ago_user_ids = long_long_time_ago_user_ids,
    ).fetchall()
    # print(not_bill_user_ids_result)

    give_voucher_sql = text("""
        insert into
            vouchers
        (
            user_id, bid_point, expired_date, operator_id,
            status, grant_date, reason
        ) values (
            :user_id, 200, '2018-08-16', 0,
            1, '2018-07-18', 6
        ),(
            :user_id, 200, '2018-08-16', 0,
            1, '2018-07-18', 6
        ),(
            :user_id, 200, '2018-08-16', 0,
            1, '2018-07-18', 6
        );
    """)
    for i in not_bill_user_ids_result:
        conn.execute(
            give_voucher_sql,
            user_id=i.id
        )
        if i.wx_openid:
            wx_openids.append(i.wx_openid)

    # 更新代金券日统计
    find_voucher_day_stat_sql = text("""
        select id from voucher_day_stat where stat_date='2018-07-18';
    """)

    insert_initial_voucher_day_stat_sql = text("""
        insert into voucher_day_stat (stat_date) values ('2018-07-18');
    """)

    update_voucher_day_stat_sql = text("""
        update
            voucher_day_stat
        set
            grant_user_cnt=:grant_user_cnt,
            grant_cnt=:grant_cnt
        where
            stat_date='2018-07-18';
    """)

    tmp_result = conn.execute(find_voucher_day_stat_sql).fetchone()
    if not tmp_result:
        conn.execute(insert_initial_voucher_day_stat_sql)
    conn.execute(
        update_voucher_day_stat_sql,
        grant_user_cnt=len(not_bill_user_ids_result),
        grant_cnt=len(not_bill_user_ids_result) * 3,
    )

    # 虚拟用户和内部用户汇币清零
    update_robot_pack_sql = text("""
        update
            pack
        set
            bid_point=0
        where
            uid in (
                select id from user where
                is_internal=1 or is_virt=1
            );
    """)
    conn.execute(update_robot_pack_sql)

    # 微信给用户打标签
    # print(len(wx_openids))
    # for i in range(0, len(wx_openids), 50):
    #     update_users_tag(wx_openids[i:i+50], wx_tag_id)


if __name__ == '__main__':
    # main()
    pass

